# -*- coding: utf-8 -*-

__author__ = "Sérgio Severino, 9733"
__version__ = "v1.0 from 6/12/2011"

import sqlite3
import csv
import glob
from operator import itemgetter


class DB:
    '''
    A class to store some constants
    '''
    TEACHER_CODE = 0
    NAME = 1
    DEGREE = 2
    COURSE = 3
    CATEGORIE = 4
    WORK_TIME = 5
    YEAR = 6
    ESTABLISHMENT_TYPE = 7
    ESTABLISHMENT_NAME = 8
    pass



class Database:
    '''
    The class that manages all the sqlite and csv files work
    '''
    
    @staticmethod
    def csvFolderToSQliteDB( folder, sqLiteDB ):
        '''
        A static method that converts a set of csv files
        inside a folder to a sqlite database
        receives a folder name
        receives a sqlite filename
        '''

        #csv file header
        titles = []
        titlesAppend = titles.append
        
        connection = sqlite3.connect(sqLiteDB)
        cursor = connection.cursor()
        cursorExec = cursor.execute

        cursorExec('''drop table if exists tabela''')
        connection.commit()
        cursorExec('''create table if not exists tabela (
                            CodigodoDocente text,
                            Nome text,
                            UltimoGrau text,
                            Curso text,
                            Categoria text,
                            RegimeDeTempo text,
                            AnoLectivo text,
                            TipoDeEstabelecimento text,
                            NomeDoEstabelecimento text )''')

        csvRead = csv.reader
        
        # iterates over all csv files in the folder
        for filename in glob.glob( folder + "/*.csv" ):
            with open(filename, 'rb') as files:
                reader = csvRead(files, delimiter=',')
                nLine = 0
                for line in reader:
                    lineList = []
                    lineListAppend = lineList.append
                    
                    if nLine == 0 and not titles:
                        for column in line:
                            lineListAppend(str(column))
                            pass
                        
                        titlesAppend(lineList) #csv header
                        pass
                    
                    elif nLine != 0:
                        
                        stmt = '''insert or replace into tabela (
                                    CodigodoDocente,
                                    Nome,
                                    UltimoGrau,
                                    Curso,
                                    Categoria,
                                    RegimeDeTempo,
                                    AnoLectivo,
                                    TipoDeEstabelecimento,
                                    NomeDoEstabelecimento)
                values("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                                format(str(line[DB.TEACHER_CODE]).replace( '"', '☼' ),# replace to prevent some errors
                                       str(line[DB.NAME]).replace( '"', '☼' ),
                                       str(line[DB.DEGREE]).replace( '"', '☼' ), 
                                       str(line[DB.COURSE]).replace( '"', '☼' ), 
                                       str(line[DB.CATEGORIE]).replace( '"', '☼' ),  
                                       str(line[DB.WORK_TIME]).replace( '"', '☼' ),
                                       str(line[DB.YEAR]).replace( '"', '☼' ),
                                       str(line[DB.ESTABLISHMENT_TYPE]).replace( '"', '☼' ),
                                       str(line[DB.ESTABLISHMENT_NAME]).replace( '"', '☼' ))
                        cursorExec(stmt)
                        pass
                    
                    nLine = nLine + 1
                    pass
                files.close()
            pass
        connection.commit()
        connection.close()

        return titles[0]

    
    def __init__( self, sqLiteDB ):
        '''
        This is the constructor
        it opens the sqlite database and copies it to a list
        '''
        
        # sqlite database file name
        self.__sqLiteDB = sqLiteDB

        # the database list
        self.__databaseList = []

        
        self.__differentYears = set()
        self.__differentEstablishmentName = set()
        self.__differentCategories = set()
        self.__differentDegree = set()
        
        connection = sqlite3.connect(sqLiteDB)
        cursor = connection.cursor()

        cursor.execute('''select * from tabela''')

        databaseListAppend = self.__databaseList.append
        
        yearsAdd = self.__differentYears.add
        establishmentAdd = self.__differentEstablishmentName.add
        categoriesAdd = self.__differentCategories.add
        degreeAdd = self.__differentDegree.add

        
        for line in cursor.fetchall():
            lineList = []
            lineListAppend = lineList.append
            for column in line:
                # from unicode to utf-8, and undoes the previous replace
                lineListAppend( column.encode('utf-8').replace( '☼', '"' ) )
                pass
            databaseListAppend( lineList )

            yearsAdd(str(lineList[DB.YEAR]))
            establishmentAdd(str(lineList[DB.ESTABLISHMENT_NAME]))
            categoriesAdd(str(lineList[DB.CATEGORIE]))
            degreeAdd(str(lineList[DB.DEGREE]))
            
            pass
        
        self.__differentYears = list( self.__differentYears )
        self.__differentYears.sort()
        
        self.__differentEstablishmentName = list( self.__differentEstablishmentName )
        self.__differentEstablishmentName.sort()
        
        self.__differentCategories = list( self.__differentCategories )
        self.__differentCategories.sort()
        
        self.__differentDegree = list( self.__differentDegree )
        self.__differentDegree.sort()
        
        connection.commit()
        connection.close()
        
        pass
    
    def getDifYears(self):
        '''
        Return method for the list __differentYears
        '''
        return self.__differentYears
    
    
    def getDifEstablishmentName(self):
        '''
        Return method for the list __differentEstablishmentName
        '''
        return self.__differentEstablishmentName
    

    def getDifCategories(self):
        '''
        Return method for the list __differentCategories
        '''
        return self.__differentCategories
    

    def getDifDegree(self):
        '''
        Return method for the list __differentDegree
        '''
        return self.__differentDegree
    

    def getNTheachersPerEstablishmentPerYear(self, establishmentName, year):
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName and in the given year
        then returns their quantity
        '''
        return len(filter(lambda line: line[DB.YEAR] == year,
                          filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                 self.getDBList())))

    def getNTheachersPerCategoriePerEstablishmentPerYear(self, categorie, establishmentName, year):
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName, year and categorie
        then returns their quantity
        '''
        return len(filter(lambda line: line[DB.CATEGORIE] == categorie,
                          filter(lambda line: line[DB.YEAR] == year,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                        self.getDBList()) )))

    def getNTheachersPerDegreePerEstablishmentPerYear(self, degree, establishmentName, year):
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName, year and degree
        then returns their quantity
        '''
        return len(filter(lambda line: line[DB.DEGREE] == degree,
                          filter(lambda line: line[DB.YEAR] == year,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                        self.getDBList()))))
    

    def getDBList(self):
        '''
        returns a list with the database
        '''
        return self.__databaseList
    
    
    def getDBLenght(self):
        '''
        returns the database lenght
        '''
        return len(self.getDBList())


##    def createCsvFileOfNTheachersPerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per establishment per year listed in the database
##        '''
##        tmpList = []
##
##        getNTheachersPerEstablishmentPerYear = self.getNTheachersPerEstablishmentPerYear
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##
##        for establishment in establishments:
##            for year in years:
##                quantity = getNTheachersPerEstablishmentPerYear(establishment, year)
##                append([establishment, year, str(quantity)])
##                pass
##            pass
##
##
##
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass
##
##
##    def createCsvFileOfNTheachersPerCategoriePerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per categorie per establishment per year listed in the database
##        '''
##        tmpList = []
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##        categories = self.getDifCategories()
##        getNTheachersPerCategoriePerEstablishmentPerYear = self.getNTheachersPerCategoriePerEstablishmentPerYear
##        
##        for categorie in categories:
##            for establishment in establishments:
##                for year in years:
##                    quantity = getNTheachersPerCategoriePerEstablishmentPerYear(categorie, establishment, year)
##                    append([categorie, establishment, year, str(quantity)])
##                pass
##            pass
##        
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass
##    
##
##    def createCsvFileOfNTheachersPerDegreePerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per degree per establishment per year listed in the database
##        '''
##        tmpList = []
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##        degrees = self.getDifDegree()
##        getNTheachersPerDegreePerEstablishmentPerYear = self.getNTheachersPerDegreePerEstablishmentPerYear
##
##        for degree in degrees:
##            for establishment in establishments:
##                for year in years:
##                    quantity = getNTheachersPerDegreePerEstablishmentPerYear(categorie, establishment, year)
##                    append([degree, establishment, year, str(quantity)])
##                    pass
##                pass
##            pass
##        
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass


    def createCsvFile( self, filename, filelist ):
        '''
        This method creates a csv file 
        '''
        csvFile = open( filename, 'wb')
        
        spamWriter = csv.writer( csvFile, delimiter=',' )
        spamWriter.writerows(filelist)

        csvFile.close()
        pass

    def createCSVStatsPerEstablishment(self, establishment ):
        '''
        This method produces the statistics about
        the given argument
        '''

        tmpList = []

        getNTheachersPerEstablishmentPerYear = self.getNTheachersPerEstablishmentPerYear
        append = tmpList.append
        append( ['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerEstablishmentPerYear(establishment, year)
            append([establishment, year, str(quantity)])
            pass
        filename = '%s stats.csv' % establishment
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerEstablishment( self, establishment ):
        '''
        This method produces a list with all teachers in
        the given argument
        '''

        tmpList = filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList())
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(1, 2)) 
        tmpList1.insert( 0, ['Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s teacher list.csv' % establishment
        self.createCsvFile( filename, tmpList1 )

        pass

    def createCSVStatsPerCategoriePerEstablishment(self, categorie, establishment ):
        '''
        This method produces the statistics about
        the given arguments
        '''
        tmpList = []

        getNTheachersPerCategoriePerEstablishmentPerYear = self.getNTheachersPerCategoriePerEstablishmentPerYear
        append = tmpList.append
        append( ['Categoria', 'Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerCategoriePerEstablishmentPerYear(categorie, establishment, year)
            append([categorie, establishment, year, str(quantity)])
            pass
        filename = '%s in %s stats.csv' % (categorie, establishment)
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerCategoriePerEstablishment( self, categorie, establishment ):
        '''
        This method produces a list with all teachers in
        the given arguments
        '''

        tmpList = filter(lambda line: line[DB.CATEGORIE] == categorie,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList()))
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.CATEGORIE], line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(2, 3)) 
        tmpList1.insert( 0, ['Categoria', 'Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s in %s teacher list.csv' % (categorie, establishment)
        self.createCsvFile( filename, tmpList1 )

        pass

    def createCSVStatsPerDegreePerEstablishment(self, degree, establishment ):
        '''
        This method produces the statistics about
        the given arguments
        '''

        tmpList = []

        getNTheachersPerDegreePerEstablishmentPerYear = self.getNTheachersPerDegreePerEstablishmentPerYear
        append = tmpList.append
        append( ['Ultimo grau', 'Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerDegreePerEstablishmentPerYear(degree, establishment, year)
            append([degree, establishment, year, str(quantity)])
            pass
        filename = '%s in %s stats.csv' % (degree, establishment)
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerDegreePerEstablishment( self, degree, establishment ):
        '''
        This method produces a list with all teachers in
        the given arguments
        '''

        tmpList = filter(lambda line: line[DB.DEGREE] == degree,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList()))
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.DEGREE], line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(2, 3)) 
        tmpList1.insert( 0, ['Ultimo grau', 'Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s in %s teacher list.csv' % (degree, establishment)
        self.createCsvFile( filename, tmpList1 )

        pass
    
